#!/bin/bash
# File Name   : analysis_binlog.sh
# Author      : moshan
# Mail        : mo_shan@yeah.net
# Created Time: 2019-04-11 18:34:42
# Function    : Analysis binlog for MySQL
#########################################################################
work_dir='/data/git/analysis_binlog'
. ${work_dir}/function/logging.sh
localhost_ip="${5}"
log_file="${7}"
thread_num="${9}"
function f_analysis_binlog()
{
    mysqlbinlog="${1}"
    binlog_file="${2}"
    show="${3}"
    res_file="${4}"
    sort_pos="${5}"
    record_type="${6}"
    eval ${mysqlbinlog} --base64-output=decode-rows -vv ${binlog_file} 2>/dev/null|awk -v show_opt="${show}" -v sort_opt="${sort_pos}" -v record_type="${record_type}" 'BEGIN {
        table=1;
        trs_count=0;
        trs_insert_count=0;
        trs_delete_count=0;
        trs_update_count=0;
        show_first_time="First Time";
        show_last_time="Last Time";
        show_table="Table";
        show_type="Type";
        show_count="affe(row)";
        db_name="";
        t_name="";
        show_total="Trans(total)";
        show_insert="Insert(s)";
        show_update="Update(s)";
        show_delete="Delete(s)";
        show_dml_total="Total(s)";
        s_type=""; 
        s_count=0;
        count=0;
        start=0;
        insert_count=0;
        update_count=0;
        delete_count=0;
        flag=0;
    }
    {
    if (show_opt=="detail" && start==0)
        {
            printf "\033[35m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\n\033[0m",show_table,show_first_time,show_last_time,show_type,show_count,show_insert,show_update,show_delete,show_total;
            start=1;
        }
        if(match($0, /^#.*server id.*Table_map:.*mapped to number/)) 
        {
            split($(NF-4),a,"`");
            db_name=a[2];
            t_name=a[4];
            t_name_tmp=(db_name"."t_name);
            flag=1;
            t_time=(substr($1,2,6)" "$2);
        }
        else if (match($0, /(### INSERT INTO .*\..*)/)) 
        {
            count=count+1;
            insert_count=insert_count+1;
            s_type="INSERT"; 
            s_count=s_count+1;
        }
        else if (match($0, /(### UPDATE .*\..*)/)) 
        {
            count=count+1;
            update_count=update_count+1;
            s_type="UPDATE"; 
            s_count=s_count+1;
        }
        else if (match($0, /(### DELETE FROM .*\..*)/)) 
        {
            count=count+1;
            delete_count=delete_count+1;
            s_type="DELETE"; 
            s_count=s_count+1;
        }
        else if (match($0, /^(# at) /) && flag==1 && s_count>0) 
        {
            opt_insert=(t_name_tmp"_insert");
            opt_update=(t_name_tmp"_update");
            opt_delete=(t_name_tmp"_delete");
            opt_trs_insert=(t_name_tmp"_trs_insert");
            opt_trs_update=(t_name_tmp"_trs_update");
            opt_trs_delete=(t_name_tmp"_trs_delete");
            opt_first_time=(t_name_tmp"_first_time");
            opt_last_time=(t_name_tmp"_last_time");
            if (table>1)
            {
                mark=0;
                for (table_tmp=1;table_tmp<=table;table_tmp++)
                {
                    if (index(t_name_array[table_tmp],t_name_tmp))
                    {
                        mark=1;
                        break;
                    }
                }
                if(mark==0)
                {
                    res[opt_insert]=0;
                    res[opt_update]=0;
                    res[opt_delete]=0;
                    res[opt_trs_insert]=0;
                    res[opt_trs_update]=0;
                    res[opt_trs_delete]=0;
                    res[opt_last_time]="";
                    res[opt_first_time]=t_time;
                    t_name_array[table]=t_name_tmp;
                    table++;
                    mark=0;
                }
            }
            else if(table==1)
            {
                res[opt_insert]=0;
                res[opt_update]=0;
                res[opt_delete]=0;
                res[opt_trs_insert]=0;
                res[opt_trs_update]=0;
                res[opt_trs_delete]=0;
                res[opt_last_time]=""
                res[opt_first_time]=t_time;
                t_name_array[1]=t_name_tmp;
                table++;
            }
            if (s_type=="DELETE")
            {
                res[opt_delete]+=s_count;
            }
            else if (s_type=="UPDATE")
            {
                res[opt_update]+=s_count;
            }
            else if (s_type=="INSERT")
            {
                res[opt_insert]+=s_count;
            }
            res[opt_last_time]=t_time;
            if (show_opt=="detail")
            {
                printf "\033[32m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\n\033[0m",t_name_tmp,t_time,t_time,s_type,s_count,res[opt_insert],res[opt_update],res[opt_delete],trs_count;
            }
            s_type_tmp=s_type;
            s_type=""; 
            s_count=0; 
        }
        else if (match($0, /^(COMMIT)/)) 
        {
            if (s_type_tmp=="DELETE")
            {
                trs_delete_count+=1;
                res[opt_trs_delete]+=1;
            }
            else if (s_type_tmp=="UPDATE")
            {
                trs_update_count+=1;
                res[opt_trs_update]+=1;
            }
            else if (s_type_tmp=="INSERT")
            {
                trs_insert_count+=1;
                res[opt_trs_insert]+=1;
            }
            trs_count+=1;
            count=0;
            insert_count=0;
            update_count=0; 
            delete_count=0;
            s_type=""; 
            s_count=0; 
            flag=0;
        }
    } END{
        t_name_array_length=length(t_name_array);
        if (show_opt=="detail")
        {
            print "";
        }
        printf "\033[35m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\033[0m\n",show_table,show_first_time,show_last_time,show_type,show_count,show_insert,show_update,show_delete,show_dml_total;
        if (sort_opt=="update")
        {
            sort_pos=9;
        }
        else if (sort_opt=="delete")
        {
            sort_pos=10;
        }
        else if (sort_opt=="insert")
        {
            sort_pos=8;
        }
        else
        {
            sort_pos=11;
        }
        if (record_type=="count" || record_type=="c")
        {
            for (i=1;i<=t_name_array_length;i++)
            {
                opt_insert=(t_name_array[i]"_insert");
                opt_update=(t_name_array[i]"_update");
                opt_delete=(t_name_array[i]"_delete");
                opt_first_time=(t_name_array[i]"_first_time");
                opt_last_time=(t_name_array[i]"_last_time");
                res_dml_total=res[opt_insert]+res[opt_delete]+res[opt_update];
                printf "\033[32m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\n\033[0m",t_name_array[i],res[opt_first_time],res[opt_last_time],"DML","0",res[opt_insert],res[opt_update],res[opt_delete],res_dml_total | "sort -rn -k"sort_pos;
            }
        }
        else 
        {
            for (i=1;i<=t_name_array_length;i++)
            {
                opt_trs_insert=(t_name_array[i]"_trs_insert");
                opt_trs_update=(t_name_array[i]"_trs_update");
                opt_trs_delete=(t_name_array[i]"_trs_delete");
                opt_first_time=(t_name_array[i]"_first_time");
                opt_last_time=(t_name_array[i]"_last_time");
                res_trs_dml_total=res[opt_trs_insert]+res[opt_trs_delete]+res[opt_trs_update];
                printf "\033[32m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\n\033[0m",t_name_array[i],res[opt_first_time],res[opt_last_time],"DML","0",res[opt_trs_insert],res[opt_trs_update],res[opt_trs_delete],res_trs_dml_total | "sort -rn -k"sort_pos;
            }
        }
        opt_first_time=(t_name_array[1]"_first_time");
        opt_last_time=t_time;
        printf "\033[35m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\033[0m\n",show_table,show_first_time,show_last_time,show_type,show_count,show_insert,show_update,show_delete,show_dml_total;
        printf "\033[32m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\n\033[0m","All table",res[opt_first_time],opt_last_time,"DML","0",trs_insert_count,trs_update_count,trs_delete_count,trs_count;
        print "";
    }' > ${res_file} 2>/dev/null
    if [ $? -eq 0 ]
    then
        f_logging "INFO" "THREAD_${thread_num}:Analysis completed --> ${binlog_file}" "2"|tee -a ${log_file}
    else
        f_logging "WARN" "THREAD_${thread_num}:Analysis completed --> ${binlog_file}. The binlog file may be an empty transaction file."|tee -a ${log_file} 
    fi
    [ -f "${work_dir}/pid/${binlog_file}.pid" ] && rm -f ${work_dir}/pid/${binlog_file}.pid
    [ -f "${work_dir}/thread/${thread_num}" ] && rm -f ${work_dir}/thread/${thread_num}
}
f_analysis_binlog "${1}" "${2}" "${3}" "${4}" "${6}" "${8}" "${9}"
